package com.telephone.purchase.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface IndexDaoMapper {
    @Select("select count(*) from user;")
    int getAllUsers();
    @Select("select count(*) from user where to_days(create_time) = to_days(now());")
    int getNewUsers();
    @Select("select count(*) from `order`;")
    int getAllOrders();
    @Select("select count(*) from `order` where to_days(pay_time) = to_days(now());")
    int getNewOrders();
    @Select("select count(*) from user where role_id=1;")
    int getAllVIP();
    @Select("select count(*) from user where role_id=1 and to_days(create_time) = to_days(now());")
    int getNewVIP();
    @Select("select sum(pay_money) from `order`;")
    BigDecimal getAllMoney();
    @Select("select sum(pay_money) from `order` where to_days(pay_time) = to_days(now());")
    BigDecimal getDayMoney();
    @Select("SELECT DATE_FORMAT( create_time, '%Y%m' ) months, count( * ) count FROM `user` GROUP BY months ORDER BY months DESC LIMIT 0,7;")
    List<Map<String, Object>> getUserByMonths();
    @Select("SELECT DATE_FORMAT( pay_time, '%Y%m' ) months, count( * ) count FROM `order` GROUP BY months ORDER BY months DESC LIMIT 0,7;")
    List<Map<String, Object>> getOrderByMonths();
    @Select("SELECT DATE_FORMAT( pay_time, '%Y%m' ) months, sum( PAY_MONEY ) count FROM `order` GROUP BY months ORDER BY months DESC LIMIT 0,7;")
    List<Map<String, Object>> getMoneyByMonths();
    @Select("select count(order.ORDER_ID) as value,table2.GOOD_NAME as name from `order` right join (select table1.PACKAGE_ID,table1.GOOD_ID,good.GOOD_NAME from good right join (select PACKAGE_ID,GOOD_ID from package GROUP BY PACKAGE_ID) table1 on good.GOOD_ID=table1.GOOD_ID) table2 on table2.PACKAGE_ID=order.PACKAGE_ID where order.PAY_TIME>='2019-07-01 00:00:00' group by table2.GOOD_NAME;")
    List<Map> getPieGraphData();
}
